'''-- 机构或企业认证表
CREATE TABLE IF NOT EXISTS wx_organization_authentication (

    wx_id INT PRIMARY KEY, -- 微信号
    -- 机构类型
    organization_type VARCHAR(50),
    -- 机构方向
    organization_direction VARCHAR(50),
    -- 所属区域
    area VARCHAR(50),
    -- 机构地址
    address VARCHAR(2000),
    -- 机构简介
    introduction VARCHAR(2000),
    -- 主营业务
    main_business VARCHAR(2000),
    -- 联系人姓名
    contact_name VARCHAR(50),
    -- 联系电话
    contact_number VARCHAR(50),
    -- 联系人职位
    contact_job VARCHAR(50),
    -- 认证状态
    authentication_status BOOLEAN DEFAULT false,
    -- 认证通过时间
    authentication_time TIMESTAMP,
    -- 信息提交时间
    submit_time TIMESTAMP DEFAULT now(),

    FOREIGN KEY (wx_id) REFERENCES wx_user(wx_id)

);'''

import datetime
import logging
import psycopg2


logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
class WxOrganizationAuthenticationMethods: 

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn


    def get_wx_organization_authentication_by_wx_id(self, wx_id):
        
        sql = "SELECT * FROM wx_organization_authentication WHERE wx_id = %s"
        self.cursor.execute(sql, (wx_id,))
        result = self.cursor.fetchone()
        return result

    def insert_or_update_wx_organization_authentication(self, wx_id, organization_name, organization_direction, area, address, introduction, main_business, contact_name, contact_number, contact_job, business_license_photo):

        if self.get_wx_organization_authentication_by_wx_id(wx_id):
            sql = "UPDATE wx_organization_authentication SET organization_name=%s, organization_direction = %s, area = %s, address = %s, introduction = %s, main_business = %s, contact_name = %s, contact_number = %s, contact_job = %s, business_license_photo = %s WHERE wx_id = %s"
            self.cursor.execute(sql, (organization_name, organization_direction, area, address, introduction, main_business, contact_name, contact_number, contact_job,business_license_photo, wx_id))
            self.conn.commit()
            logger.info("更新机构认证信息成功")
        else:
            sql = "INSERT INTO wx_organization_authentication (wx_id, organization_name, organization_direction, area, address, introduction, main_business, contact_name, contact_number, contact_job, business_license_photo) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            self.cursor.execute(sql, (wx_id, organization_name, organization_direction, area, address, introduction, main_business, contact_name, contact_number, contact_job, business_license_photo))
            self.conn.commit()
            logger.info("新增机构认证信息成功")


    def update_authentication_status(self, wx_id, authentication_status):
        sql = "UPDATE wx_organization_authentication SET authentication_status = %s, authentication_time = %s WHERE wx_id = %s"
        self.cursor.execute(sql, (authentication_status, datetime.datetime.now(), wx_id))
        self.conn.commit()
        logger.info("更新认证状态成功")


    # 获取所有机构认证信息
    def get_all_wx_organization_authentication(self, page, limit):
        sql = "SELECT * FROM wx_organization_authentication order by submit_time desc limit %s offset %s"
        self.cursor.execute(sql, (limit, (page-1)*limit))
        result = self.cursor.fetchall()
        return result

    
    # 数量 统计
    def get_count_wx_organization_authentication(self):
        sql = "SELECT COUNT(*) FROM wx_organization_authentication"
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        return result['count']
    
    # 获取所有认证状态为True的机构认证信息
    def get_all_authentication_status_true(self, page, limit):
        sql = "SELECT * FROM wx_organization_authentication WHERE authentication_status = true order by submit_time desc limit %s offset %s"
        self.cursor.execute(sql, (limit, (page-1)*limit))
        result = self.cursor.fetchall()
        return result

    
    # 获取所有认证状态为True的机构认证信息数量
    def get_count_authentication_status_true(self):
        sql = "SELECT COUNT(*) FROM wx_organization_authentication WHERE authentication_status = true"
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        return result['count']
    
    # 获取所有认证状态为False的机构认证信息
    def get_all_authentication_status_false(self, page, limit):
        sql = "SELECT * FROM wx_organization_authentication WHERE authentication_status = false order by submit_time desc limit %s offset %s"
        self.cursor.execute(sql, (limit, (page-1)*limit))
        result = self.cursor.fetchall()
        return result

    
    # 获取所有认证状态为False的机构认证信息数量
    def get_count_authentication_status_false(self):
        sql = "SELECT COUNT(*) FROM wx_organization_authentication WHERE authentication_status = false"
        self.cursor.execute(sql)
        result = self.cursor.fetchone()
        return result['count']